import flask
import sqlite3
import json
app=flask.Flask(__name__)
class StudentDB:
    def openDB(self):
        self.con=sqlite3.connect("students.db")
        self.cursor=self.con.cursor()
    def closeDB(self):
        self.con.commit()
        self.con.close()
# 服务器程序
    def initTable(self):
        res={}
        try:
            self.cursor.execute("create table students (No varchar(16) primary key,Namevarchar(16), Sex varchar(8), Age int)")
            res["msg"]="OK"
        except Exception as err:
            res["msg"]=str(err)
        return res
    def insertRow(self,No,Name,Sex,Age):
        res={}
        try:
            self.cursor.execute("insert into students (No,Name,Sex,Age) values(?,?,?,?)",(No,Name,Sex,Age))
            res["msg"]="OK"
        except Exception as err:
            res["msg"]=str(err)
        return res
    def deleteRow(self,No):
        res={}
        try:
            self.cursor.execute("delete from students where No=?",(No,))
            res["msg"]="OK"
        except Exception as err:
            res["msg"]=str(err)
        return res
    def selectRows(self):
        res={}
        try:
            data=[]
            self.cursor.execute("select * from students order by No")
            rows=self.cursor.fetchall()
            for row in rows:
                d={}
                d["No"]=row[0]
                d["Name"]=row[1]
                d["Sex"]=row[2]
                d["Age"]=row[3]
                data.append(d)
                res["msg"]="OK"
                res["data"]=data
        except Exception as err:
            res["msg"]=str(err)
        return res
    @app.route("/",methods=["GET","POST"])
    def process():
        opt=flask.request.values.get("opt") if "opt" in flask.request.values else ""
        res={}
        db = StudentDB()
        db.openDB()
        if opt=="init":
            res=db.initTable()
        elif opt=="insert":
            No=flask.request.values.get("No") if "No" in flask.request.values else ""
            Name = flask.request.values.get("Name") if "Name" in flask.request.values else ""
            Sex=flask. request.values.get("Sex") if "Sex" in flask.request.values else ""
            Age = flask.request.values.get("Age") if "Age" in flask.request.values else ""
            res=db.insertRow(No,Name,Sex,Age)
        elif opt=="delete":
            No=flask.request.values.get("No") if "No" in flask.request.values else ""
            res=db.deleteRow(No)
        else:
            res=db.selectRows()
        db.closeDB()
        return json.dumps(res)
if __name__=="__main__":
    app.run()